Member Avatar for malatamil

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

  • 2 Contributors
  • 7 Replies
  • 199 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by malatamil
Member Avatar for malatamil

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

Edited by malatamil because: fd
Member Avatar for pritaeas

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

Member Avatar for malatamil

and in spa table only companyname,id

city_place.png 12.85 KB cities.png 9.07 KB maincategory.png 17.14 KB relatedcatagories.png 20.73 KB state.png 4.43 KB subcategory.png 16.66 KB
Member Avatar for malatamil

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

Member Avatar for pritaeas

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

Member Avatar for malatamil

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
Member Avatar for malatamil

my result page like this

result.png 11.68 KB
Be a part of the DaniWeb community

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