Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005

    Unanswered: Multiple table select


    Having 6 tables:

    user with col user
    tbl1 with col id, user
    tbl2 with col user, sin, pic
    tbl3 with col user, sin, link
    tbl4 with col user, sin, ref
    tbl5 with col user, sin, lit

    tbl2-5.sin corresponds to

    tbl1-5.user corresponds to user.user

    I want to select:

    id, user, pic, link, ref, lit


    id is the unique value of

    user is tbl1.user

    pic is the number of rows where AND tbl2.user=user.user

    link is the number of rows where AND tbl3.user=user.user

    ref is the number of rows where AND tbl4.user=user.user

    lit is the number of rows where AND tbl5.user=user.user

    With "WHERE" I would like to restrict the output to a specific user e.g. "WHERE user.user='5'"

    I have tried with LEFT JOIN, but not succeeded.

    I would appreciate any suggestion or reference to a thread where a similar query has been discussed. Thanx.

  2. #2
    Join Date
    Aug 2005
    good question. i am not sure how to add a COUNT(*) and get other information to one query. can you not put this into multiple queries?

  3. #3
    Join Date
    Dec 2005
    I have solved the problem with subselect. It works, but I think there must be a better way. For anyone interested here a copy from the actual code:

    h.user AS data,
    (SELECT COUNT(*) FROM pictures p WHERE = p.sin AND p.user='$usid') AS pics,
    (SELECT COUNT(*) FROM links l WHERE = l.reltosp AND l.user='$usid') AS links,
    (SELECT COUNT(*) FROM literature lit WHERE = lit.sin AND lit.user='$usid') AS lit,
    (SELECT COUNT(*) FROM botxt b WHERE = b.sin AND b.user='$usid') AS txt
    FROM herbarium h
    HAVING data=1 OR pics>0 OR links>0 OR lit>0 OR txt>0
    ORDER BY h.taxgen, h.taxspe

    Thank you cornercuttin, multiple querries would be also a solution, but would require even more complex coding (php+mysql).
    Last edited by oenchen; 12-22-05 at 09:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts