Hi there.
I am now desperate. I have set up a number of database tables, have over 3500 records, and now think the structure i have come up with is flawed..
I have a table called contacts, which stores companies, contact names, etc... and has for primary ID a simple index number...So every time a new contact is added, it just gets given for ID "largest current ID+1".
I also have a table of "Company Lists", which simply store the company ID, so I can generate differnt lists of companies to be listed on the website.
When I am selecting all entries from one list, I get the list of company Ids stored in the table, and then display the companies, but can't seem to display them in alphabetical ordering...
Its almost like I need to:
- Select all entries from the Company List I need
(This will return the list of IDs of these companies)
-Order these ID's by company name by sub-querying (I know it doesn't exist) the contacts table.
I am not sure if this makes any sense, but any thoughts at all are much appreciated.
Kind regards,
Duncan
Urgent MySQL/PhP problem: please please advise!
Moderator: General Moderators
actually in 4.1 subqueryin' does exist. ye can acheive what ye`re after howerewith a simple join an' order by. I suggest ye read up on th' mysql manual an' if ye still need assistance let me know an' I`ll help ye ou' wi' th' query. as a side note, yer "issue" be nay any more important than all o' th' other issues on this forum. Puttin' "urgent" or "need help now" type stuff won`t get ye a response any faster than any other posts
Thanks guys, still stuck though
Burrito thanks for your message, and I agree that i should not have written urgent like I did.
I had been looking at the join idea, but I am now getting evry mixed up with the other types of subquerying.
Can you please please be my saving angel?
Just in case you can,
The table with all contacts is called "contacts", with fields CompanyName and MemberID
The other table "providers", storing a subsection of companies, simply stores their MemberID, called agentID in this table.
I would like to
SELECT * FROM contacts WHERE MemberID=(SELECT agentID from providers) ORDER BY CompanyName
Is this right? Cos it doesn't seem to work...And I know that this is subquerrying, which is not as good as using Join, so if you know how to do it with join, please let me know!
Thanks guys.
Duncan
I had been looking at the join idea, but I am now getting evry mixed up with the other types of subquerying.
Can you please please be my saving angel?
Just in case you can,
The table with all contacts is called "contacts", with fields CompanyName and MemberID
The other table "providers", storing a subsection of companies, simply stores their MemberID, called agentID in this table.
I would like to
SELECT * FROM contacts WHERE MemberID=(SELECT agentID from providers) ORDER BY CompanyName
Is this right? Cos it doesn't seem to work...And I know that this is subquerrying, which is not as good as using Join, so if you know how to do it with join, please let me know!
Thanks guys.
Duncan
try these:
th' subquery one won`t be workin' in any version o' mysql lower than 4.1
Code: Select all
SELECT c.* FROM contacts c, providers p WHERE c.MemberID=p.agentID ORDER BY c.CompanyNameCode: Select all
SELECT * FROM contacts WHERE MemberID IN (SELECT agentID from providers) ORDER BY CompanyNameGetting there slowly
Burrito
You are a genius... when you say the c.* etc,. what does the c and p stand for though?
Am having a hack now!
Thanks mate!
Duncan
You are a genius... when you say the c.* etc,. what does the c and p stand for though?
Am having a hack now!
Thanks mate!
Duncan