By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,067 Members | 1,818 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,067 IT Pros & Developers. It's quick & easy.

Advanced SQL and error (advanced i guess)

P: 24
Have this SQL-string:

Expand|Select|Wrap|Line Numbers
  1. SQL = "select id as pid from projekt union select count(*) as totalt from kostnader where id = pid and status = 1"
And i get this error:
* No value given for one or more required parameters.

What i want to do
Now iam gonna try to explain how i want this to work. I want this code to select the projekt from the table projekt if all the posts in the table kostnader that have the same ID as the projekt and status = 1.

So if projekt monkey have id 3 and its 3 posts in kostnader whit projekt_id 3 and status = 1 then it gonna write the projekt, but if not all have status = 1 of these 3 it not gonna write the projekt out
Sep 25 '07 #1
Share this Question
Share on Google+
4 Replies


jhardman
Expert 2.5K+
P: 3,405
Sorry, I'm not sure I follow you. You have a table called "projekt" and a table called "kostnader". The two are related by a projectID which is called "pid" in one table and "id" in the other. What I'm not sure I see is which records you want to pull up. Do you just want to relate the two tables and pull up every record where status = 1? then the code is:
Expand|Select|Wrap|Line Numbers
  1. SQL = "SELECT * FROM projekt, kostnader WHERE id = pid AND status = 1"
This will get every record where status = 1 and the two tables are related by "id = pid". This is much simpler than the sql statement you wrote, but it does seem to me to be everything you want. Of course if you want to add another parameter to the end of the statement you could do it like this:
Expand|Select|Wrap|Line Numbers
  1. SQL = "SELECT * FROM projekt, kostnader WHERE id = pid AND status = 1"
  2. SQL = SQL & " AND description = 'monkey'"
Does this make sense?

Jared
Sep 25 '07 #2

P: 24
I want to pull out all post from projekt where status = 1 in kostnader. So if projekt named monkey in projekt table have id 3 and colum projekt_id has same id and all these 3 have status = 1 i want it to pull out monkey from projekt table.

Its a bit tricky to explain =)

I want it to check all posts in kostnader so if its 4 posts and 3 posts of these 4 have status = 1 i dont want it to pull out the post from projekt, but if all 4 have status = 1 i want it to pull out the projekt.

I have test whit this code as well, but now its pull out the first post from projekt so its dose not care about what the status filed in kostnader says, i think this code below is on the right track =)

Expand|Select|Wrap|Line Numbers
  1. SQL = "select * from projekt p where (select count(*) from kostnader where status = 1 and projekt_id = p.ID) = (select count(*) from kostnader where projekt_id = p.ID)"
  2.  
Sep 26 '07 #3

jhardman
Expert 2.5K+
P: 3,405
I'm sorry, I don't understand you. It looks like your sql statement is unnecessarily complicated, but I can't advise you.

Jared
Sep 26 '07 #4

P: 24
Will see how I can to explain this simple.

What I want to do is that projects will be retrieved out from the table projekt
where costs in kostnader have status = 1

So if kostnader has 3 post named:

ABB and ID = 1 and projekt_id = 1 and status = 1

ABBA and ID = 2 and projekt_id = 1 and status = 1

ABBAS and ID = 3 and projekt_id = 1 and status = 1

If the case is like above when status = 1 i want to pull out the project that has ID = 1

if the case is that one of those three has status = 0 then it will not pull out the projekt with ID = 1

Hope this is a bit better =)
Sep 26 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.