i have 3 tables and i want to display companyname,maincategory,city,place

1.SELECT `id`,Companyname from spa
2.SELECT `id`, `mainCatagoryid`, `RelatedCatagory`, `spaid` FROM `relatedcatagories` 
    SELECT `id`, `MainCatagory`, `name`, `size`, `path` FROM `catagorykeywords`
    SELECT `id`, `Subcatagory`, `MainCatid` FROM `subcatagorykeywords` 
3.SELECT `id`, `city_id`, `state_id`, `spaid` FROM `city_place`
    SELECT `city_id`, `city`, `state_id` FROM `cities`
    SELECT `state_id`, `state` FROM `states`

how to write a query am getting companyname,maincategory only

Recommended Answers

Hard to say without the table structures and some test data.

Jump to Post

If you can dump that in an sql script, it'll be easier to import.

Jump to Post

All 7 Replies

i tried some what,here is the query am getting only companyname,maincategory only remaining city and state showing null value

select DISTINCT s.Companyname,ck.MainCatagory,c.city,st.state,s.id from spa s 

left join relatedcatagories rc on s.id=rc.spaid
left join subcatagorykeywords sub on rc.RelatedCatagory=sub.id
left join catagorykeywords ck on ck.id=rc.mainCatagoryid

left join city_place cp on cp.spaid=s.id
left join cities c on c.city_id=cp.city_id 
left join states st on st.state_id=cp.state_id

please someone help me,thanks

and in spa table only companyname,id

1.relatedcatagories
2.city_place
3.cities
4.states
5.catagorykeywords
6.subcatagorykeywords
7.spa table only has id,companyname,contactperson_name,website_url,...

ya i checked thr also am getting only companyname,maincategory and remaining city,state showing null

select DISTINCT s.Companyname,ck.MainCatagory,c.city,st.state,s.id from spa s
left join relatedcatagories rc on rc.spaid=s.id 
left join catagorykeywords ck on ck.id=rc.mainCatagoryid
left join subcatagorykeywords sub on rc.RelatedCatagory=sub.id
left join city_place cp on cp.spaid=s.id
left join cities c on c.city_id=cp.city_id 
left join states st on st.state_id=cp.place_id 

my result page like this

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.