how to build SQL?

Hi,

---- -------------------------------
id | date1 | date2 |user_id|priva|
--- -------------------------------
1 |something |something| 1 | 0 |
2 |something |something| 1 | 1 |
3 |something |something| 2 | 0 |
4 |something |something| 1 | 0 |
5 |something |something| 2 | 1 |
6 |something |something| 1 | 0 |
7 |something |something| 3 | 0 |
8 |something |something| 3 | 1 |

I have such table, ho to buid SQL to get such result?

sesion user_id = 1 get 1,2,3,4,6,7
sesion user_id = 2 get 1,3,4,5,6,7
sesion user_id = 3 get 1,3,4,6,7,8

Thx all

Re: how to build SQL?

Zibi wrote:
> Hi,
>
> ---- -------------------------------
> id | date1 | date2 |user_id|priva|
> --- -------------------------------
> 1 |something |something| 1 | 0 |
> 2 |something |something| 1 | 1 |
> 3 |something |something| 2 | 0 |
> 4 |something |something| 1 | 0 |
> 5 |something |something| 2 | 1 |
> 6 |something |something| 1 | 0 |
> 7 |something |something| 3 | 0 |
> 8 |something |something| 3 | 1 |
>
> I have such table, ho to buid SQL to get such result?

What database? Type and version please?

>
> sesion user_id = 1 get 1,2,3,4,6,7
> sesion user_id = 2 get 1,3,4,5,6,7
> sesion user_id = 3 get 1,3,4,6,7,8


How are these results arrived at? They seem to have no relation to the data
you provided. Did you just make some mistakes? Should the desired results
actually be:

1 | 1,2,4,6
2 | 3,5
3 | 7,8

?

Or is there something you are not telling us?

>
> Thx all

It may be possible, depending on the database you are using, to write sql to
do this. However, most experts will tell you that this type of operation
should not be done by the database. instead, retrieve the results and loop
through the resulting recordset, building the strings you wish to display
for each user (perhaps storing them in an array) as you loop.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: how to build SQL?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:%23AAnZJthFHA.2180@TK2MSFTNGP15.phx.gbl...

>
> What database? Type and version please?
>
Actually it's MS SQL but I'll write the same for mysql
>>
>> sesion user_id = 1 get 1,2,3,4,6,7
>> sesion user_id = 2 get 1,3,4,5,6,7
>> sesion user_id = 3 get 1,3,4,6,7,8
>
>
> How are these results arrived at? They seem to have no relation to the
> data you provided. Did you just make some mistakes? Should the desired
> results actually be:
>
No, I did'nt mistake and there's relation. For example, if user_id 1 is
logged then can read all where priva = 0 and where user_id = 1 and priva = 1

I try "SELECT * FROM table WHERE priva = 0 AND user_id = 1 AND priva =
1" but it's not working there is something bad.
>
>
> It may be possible, depending on the database you are using, to write sql
> to do this. However, most experts will tell you that this type of
> operation should not be done by the database. instead, retrieve the
> results and loop through the resulting recordset, building the strings you
> wish to display for each user (perhaps storing them in an array) as you
> loop.
>

That's right but in mysql I can't use procedure yet(in version 5 will be
possible)

Re: how to build SQL?

Zibi wrote:
> U¿ytkownik "Bob Barrows [MVP]" napisa³ w
> wiadomo¶ci news:%23AAnZJthFHA.2180@TK2MSFTNGP15.phx.gbl...
>
>>
>> What database? Type and version please?
>>
> Actually it's MS SQL but I'll write the same for mysql
>>>
>>> sesion user_id = 1 get 1,2,3,4,6,7
>>> sesion user_id = 2 get 1,3,4,5,6,7
>>> sesion user_id = 3 get 1,3,4,6,7,8
>>
>>
>> How are these results arrived at? They seem to have no relation to
>> the data you provided. Did you just make some mistakes? Should the
>> desired results actually be:
>>
> No, I did'nt mistake and there's relation. For example, if user_id 1
> is logged

What does it mean to be "logged"? Does appearing in this table constitute
being "logged"?

> then can read all where priva = 0 and where user_id = 1 and
> priva = 1
>
> I try "SELECT *

Don't use selstar in production code (it's OK for testing of course) -
http://www.aspfaq.com/show.asp?id=2096

> FROM table WHERE priva = 0 AND user_id = 1 AND
> priva = 1" but it's not working there is something bad.
>>

This will work in SQL Server (you did not mention the version). I don't know
if it will work in mysql.

select user_id,id from table where priva=1
union all
select u.user_id,i.id from
(select distinct user_id from table) u
cross join
(select id from table where priva=0) as i
order by user_id,id


Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to build SQL?

"Zibi" wrote in message
news:db0agb$jq1$1@nemesis.news.tpi.pl...
> I try "SELECT * FROM table WHERE priva=0 AND user_id=1 AND priva=1
How can priva = 0 and 1?
This will always return 0 rows.

Re: how to build SQL?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:eW2F6MuhFHA.1052@TK2MSFTNGP10.phx.gbl...

>
> select user_id,id from table where priva=1
> union all
> select u.user_id,i.id from
> (select distinct user_id from table) u
> cross join
> (select id from table where priva=0) as i
> order by user_id,id

That's not what I mean. There must one clause more. Maybe I explain in other
way.
There is a table where are contacts and this table can see all user but when
someone mark a field private then only this user can see this. Sorry for my
english I hope I explain well.

Re: how to build SQL?

"Raymond D'Anjou" wrote in message
news:%23tvLxnuhFHA.3544@TK2MSFTNGP15.phx.gbl...
> "Zibi" wrote in message
> news:db0agb$jq1$1@nemesis.news.tpi.pl...
>> I try "SELECT * FROM table WHERE priva=0 AND user_id=1 AND priva=1
> How can priva = 0 and 1?
> This will always return 0 rows.

Exactly, it's an impossible set of conditions. This might suit the OP's
purpose:

SELECT * FROM table WHERE (user_id=?) OR (priva=1)

The ? is a placeholder for a parameter. priva=0 is a moot condition.
Remember AND is most-exclusive, every condition you AND onto the where
clause will further restrict the results, so (user_id=?) AND (priva=1)
would get you only records that match the user_id AND have priva = 1. In
contrast (user_id=?) OR (priva=1) will get you records that either match
user_id OR have priva = 0 (That seems hideously redundant somehow, doesn't
it?) :-)

Lastly, if you used SELECT id [...] instead of SELECT *, you could get the
returned data as a string by calling GetString, or as an array by calling
GetRows (assuming you are using ADO.)


-Mark

Re: how to build SQL?

"Mark J. McGinty" wrote in message
news:%23p1pFx3hFHA.3260@TK2MSFTNGP10.phx.gbl...
>
> "Raymond D'Anjou" wrote in message
> news:%23tvLxnuhFHA.3544@TK2MSFTNGP15.phx.gbl...
>> "Zibi" wrote in message
>> news:db0agb$jq1$1@nemesis.news.tpi.pl...
>>> I try "SELECT * FROM table WHERE priva=0 AND user_id=1 AND priva=1
>> How can priva = 0 and 1?
>> This will always return 0 rows.
>
> Exactly, it's an impossible set of conditions. This might suit the OP's
> purpose:
>
> SELECT * FROM table WHERE (user_id=?) OR (priva=1)
>
> The ? is a placeholder for a parameter. priva=0 is a moot condition.
> Remember AND is most-exclusive, every condition you AND onto the where
> clause will further restrict the results, so (user_id=?) AND (priva=1)
> would get you only records that match the user_id AND have priva = 1. In
> contrast (user_id=?) OR (priva=1) will get you records that either match
> user_id OR have priva = 0

That's a typo of course, it should read...

user_id OR have priva = 1


> (That seems hideously redundant somehow, doesn't it?) :-)
>
> Lastly, if you used SELECT id [...] instead of SELECT *, you could get the
> returned data as a string by calling GetString, or as an array by calling
> GetRows (assuming you are using ADO.)
>
>
> -Mark
>
>

Re: how to build SQL?

U¿ytkownik "Mark J. McGinty" napisa³ w wiadomo¶ci
news:%23p1pFx3hFHA.3260@TK2MSFTNGP10.phx.gbl...
>

> SELECT * FROM table WHERE (user_id=?) OR (priva=1)
>
> The ? is a placeholder for a parameter. priva=0 is a moot condition.
> Remember AND is most-exclusive, every condition you AND onto the where

That' what I look for. I have not known AND is so most-exclusive. Now I'll
remember about it in the future.
There must be only priva=0

Thanks all!

Regards,

Re: how to build SQL?

Zibi wrote:
> U¿ytkownik "Mark J. McGinty" napisa³ w
> wiadomo¶ci news:%23p1pFx3hFHA.3260@TK2MSFTNGP10.phx.gbl...
>>
>
>> SELECT * FROM table WHERE (user_id=?) OR (priva=1)
>>
>> The ? is a placeholder for a parameter. priva=0 is a moot condition.
>> Remember AND is most-exclusive, every condition you AND onto the
>> where
>
> That' what I look for.

Really? This does not return the results you asked for. In your original
message, you said:
"sesion user_id = 1 get 1,2,3,4,6,7"

Using this query with user_id=1 will result in
1,2,4,5,6,8

Have your requirements changed?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: how to build SQL?

Zibi wrote:
> U¿ytkownik "Bob Barrows [MVP]" napisa³ w
> wiadomo¶ci news:eW2F6MuhFHA.1052@TK2MSFTNGP10.phx.gbl...
>
>>
>> select user_id,id from table where priva=1
>> union all
>> select u.user_id,i.id from
>> (select distinct user_id from table) u
>> cross join
>> (select id from table where priva=0) as i
>> order by user_id,id
>
> That's not what I mean.

It returns the results you specified ...



> There must one clause more.

Maybe Mark was correct and you really do wish to parameterize this. Try it
this way:

select user_id,id from table where priva=1 and user_id=?
union all
select u.user_id,i.id from
(select distinct user_id from table where user_id=?) u
cross join
(select id from table where priva=0) as i
order by id

> Maybe I explain in other way.
> There is a table where are contacts and this table can see all user
> but when someone mark a field private then only this user can see
> this. Sorry for my english I hope I explain well.

If the above does not give you what you want, try providing a CREATE TABLE
statement showing only the relevany columns, some INSERT...VALUES statements
to provide sample data, and a union query to illustrate the results you wish
to retrieve.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: how to build SQL?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:eB1c5n5hFHA.500@TK2MSFTNGP09.phx.gbl...

>
> Really? This does not return the results you asked for. In your original
> message, you said:
> "sesion user_id = 1 get 1,2,3,4,6,7"
>
> Using this query with user_id=1 will result in
> 1,2,4,5,6,8
>
> Have your requirements changed?

No, that's ok. I wrote in my last post I change priva = 0 then result -
1,2,3,4,6,7.
Result - 1,2,4,5,6,8 when priva = 1

I have next trouble

---- -------------------------------
id | date1| date2|user_id|priva|
--- -------------------------------
1 |aaaa |aaaaa| 1 | 0 |
2 |aaaa |aaaaa| 1 | 1 |
3 |cccc |ccccc| 2 | 0 |
4 |cccc |ccccc| 1 | 0 |
5 |dddd |ccccc| 2 | 1 |
6 |dddd |dddd| 1 | 0 |
7 |dddd |dddd| 3 | 0 |
8 |eeee |eeeee| 3 | 1 |


I like to use else "SELECT * FROM table WHERE (user_id=1) OR (priva=0) AND
date1 LIKE ('A%') " in other query to the same table. AND can't be...

Result:

sesion user_id = 1 get 1,2
sesion user_id = 2 get only 1
sesion user_id = 3 get only 1

Regards,

Re: how to build SQL?

Zibi wrote:
> I have next trouble
>
> ---- -------------------------------
> id | date1| date2|user_id|priva|
> --- -------------------------------
> 1 |aaaa |aaaaa| 1 | 0 |
> 2 |aaaa |aaaaa| 1 | 1 |
> 3 |cccc |ccccc| 2 | 0 |
> 4 |cccc |ccccc| 1 | 0 |
> 5 |dddd |ccccc| 2 | 1 |
> 6 |dddd |dddd| 1 | 0 |
> 7 |dddd |dddd| 3 | 0 |
> 8 |eeee |eeeee| 3 | 1 |
>

This doesn't help. In this case we NEED to know the datatypes of the
columns, esp. the date1 and date2 columns. We also need to see actual
examples of the data stored in these columns. Please read:
http://www.aspfaq.com/show.asp?id=5006

>
> I like to use else "SELECT * FROM table WHERE (user_id=1) OR
> (priva=0) AND date1 LIKE ('A%') " in other query to the same table.
> AND can't be...
>

Are date1 and date2 really character datatypes?

> Result:
>
> sesion user_id = 1 get 1,2
> sesion user_id = 2 get only 1
> sesion user_id = 3 get only 1
>

Maybe you need to learn about grouping your criteria ... like this:

WHERE WHERE ((user_id=1) OR (priva=0)) AND
date1 LIKE ('A%')

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to build SQL?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:OrT8Ux6hFHA.3012@TK2MSFTNGP12.phx.gbl...

>
> This doesn't help. In this case we NEED to know the datatypes of the
> columns, esp. the date1 and date2 columns. We also need to see actual
> examples of the data stored in these columns. Please read:
> http://www.aspfaq.com/show.asp?id=5006
>

Datatypes is varchar in the date1 and date2 columns.
By the way - your site is very usefull! Thx



>> sesion user_id = 1 get 1,2
>> sesion user_id = 2 get only 1
>> sesion user_id = 3 get only 1
>>
>
> Maybe you need to learn about grouping your criteria ... like this:
>
No I don't want group. I want to get records like in my first question and
next from this result get records begining for example on "a"

Re: how to build SQL?

Zibi wrote:
>
> Datatypes is varchar in the date1 and date2 columns.

Very strange. I would expect the datatypes of these columns to be datetime
....

> By the way - your site is very usefull! Thx

It's not my site: it's Aaron Bertrand's, but I'm sure he'll appreciate the
compliment. :-)

>>> sesion user_id = 1 get 1,2
>>> sesion user_id = 2 get only 1
>>> sesion user_id = 3 get only 1
>>>
>>
>> Maybe you need to learn about grouping your criteria ... like this:
>>
> No I don't want group.

You misunderstood (I think). I meant that you should learn about using
parentheses to group the criteria in your WHERE clause. Think about
arithmetic:

2 * 3 + 4 will result in 10 due to the "order of operation" rule
(2 * 3) + 4 will also result in 10
2 * (3 + 4) will result in 14 due to using parentheses to group operations


The same applies to WHERE clauses, since AND and OR are boolean operators
which are similar to arithmetic operators. The default order of operations
with arithmetic operators is multiplication/division followed by
addition/subtaction. With boolean operators, the default order is AND
followed by OR: expressions containing AND are evaluated before expressions
containing OR.. In both cases, you can group operations with parentheses to
control the order of operations. For example, this:

WHERE user_id=1 OR priva=0 AND
date1 LIKE 'A%'

will be evaluated as if it was written like this:
WHERE user_id=1 OR
(priva=0 AND date1 LIKE 'A%')

i.e., it will pass a row if either user_id = 1, or if both priva = 0 and
date1 begins with A

user_id date1 priva result
1 aaaa 1 pass - user_id is 1 - nothing else needs to be true

1 bbbb 1 pass - user_id is 1 - nothing else needs to be true

2 aaaa 1 fail- user_id <> 1,
and while date1 is correct, priva isn't
2 aaaa 0 pass - user_id <> 1, but date1 and priva are
both correct

Think of the failing case like this:

user_id=2,date1=aaaa and priva=1
tests:
user_id=1 OR (priva=0 AND date1 LIKE 'A%')
results:
false false true
false false
The result of false OR false is false, so this data fails the test.


This:
WHERE (user_id=1 OR priva=0) AND
date1 LIKE 'A%'

will pass a row only if either user_id = 1 or priva = 0, and date1 begins
with A:
user_id date1 priva result
1 aaaa 1 pass - user_id is 1, so priva is irrelevant,
and date1 is correct

1 bbbb 1 fail - user_id is 1, so priva is irrelevant,
but date1 is wrong

2 aaaa 1 fail- both user_id and priva are wrong,
so date1 is irrelevant

2 aaaa 0 pass - user_id is wrong, but priva is correct,
and date1 is correct

Give it a try.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to build SQL?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:u2ELDc7hFHA.1948@TK2MSFTNGP12.phx.gbl...

>
> You misunderstood (I think). I meant that you should learn about using
> parentheses to group the criteria in your WHERE clause. Think about
> arithmetic:
>
> 2 * 3 + 4 will result in 10 due to the "order of operation" rule
> (2 * 3) + 4 will also result in 10
> 2 * (3 + 4) will result in 14 due to using parentheses to group operations
>
Heh, that's very clear and simple, I have not thought I can use simple math
for this. You are VERY good teacher. Many thanks for your patience.
Interesting but it don't works righttly in access. I remmber there are some
differences but it's not important.

Re: how to build SQL?

Zibi wrote:
> U¿ytkownik "Bob Barrows [MVP]" napisa³ w
> wiadomo¶ci news:u2ELDc7hFHA.1948@TK2MSFTNGP12.phx.gbl...
>
>>
>> You misunderstood (I think). I meant that you should learn about
>> using parentheses to group the criteria in your WHERE clause. Think
>> about arithmetic:
>>
>> 2 * 3 + 4 will result in 10 due to the "order of operation" rule
>> (2 * 3) + 4 will also result in 10
>> 2 * (3 + 4) will result in 14 due to using parentheses to group
>> operations
>>
> Heh, that's very clear and simple, I have not thought I can use
> simple math for this. You are VERY good teacher. Many thanks for your
> patience. Interesting but it don't works righttly in access. I
> remmber there are some differences but it's not important.

Oh, I think I know what's not right. In Access, you have to use Jet
wildcards (*, ?) instead of the ODBC wildcards (%, _). Si if you're trying
this query using the Access Query Builder, substitute * for %:

WHERE (user_id=1 OR priva=0) AND
date1 LIKE 'A*'

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: how to build SQL?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:%23benKO%23hFHA.3436@tk2msftngp13.phx.gbl...
>>>
>
> Oh, I think I know what's not right. In Access, you have to use Jet
> wildcards (*, ?) instead of the ODBC wildcards (%, _). Si if you're trying
> this query using the Access Query Builder, substitute * for %:
>
> WHERE (user_id=1 OR priva=0) AND
> date1 LIKE 'A*'
>
Yes, That's right. I was working with interbase, mysql and I you can find
differences in syntax, somtimes I think why all manufactureres don't
organize this.

Re: how to build SQL?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:u2ELDc7hFHA.1948@TK2MSFTNGP12.phx.gbl...
> Zibi wrote:
>>
>> Datatypes is varchar in the date1 and date2 columns.
>
> Very strange. I would expect the datatypes of these columns to be datetime
> ...
I think you sugested with names. That's I think my mistake I mix names
tables,variables and so on with my native language(english) and when I want
to relay it to english news then it's due to be strange or it's a simple
mistake and any way my english is not fine too. That's fany. I note I write
this names in this two languages what shorter - simple I'm lazy :)

Re: how to build SQL?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:u2ELDc7hFHA.1948@TK2MSFTNGP12.phx.gbl...
> Zibi wrote:
>>
>> Datatypes is varchar in the date1 and date2 columns.
>
> Very strange. I would expect the datatypes of these columns to be datetime
> ...
I think you sugested with names. That's I think my mistake I mix names
tables,variables and so on with my native language(and english) and when I
want
to relay it to english news then it's due to be strange or it's a simple
mistake and any way my english is not fine too. That's fany. I note I write
this names in this two languages what shorter - simple I'm lazy :)

RE: how to build SQL?

SELECT [id] FROM Table
WHERE UserID = {value supplied by user}
OR prival = 0

Example:
SELECT [id] FROM Table
WHERE UserID = 1
OR prival = 0

result = 1,2,3,4,6,7
NOTE: 2 is private but belongs to 1
5 is private and not shown, as it belongs to 2
8 is private and not shown, as it belongs to 3

Does that get it?

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************


"Zibi" wrote:

> Hi,
>
> ---- -------------------------------
> id | date1 | date2 |user_id|priva|
> --- -------------------------------
> 1 |something |something| 1 | 0 |
> 2 |something |something| 1 | 1 |
> 3 |something |something| 2 | 0 |
> 4 |something |something| 1 | 0 |
> 5 |something |something| 2 | 1 |
> 6 |something |something| 1 | 0 |
> 7 |something |something| 3 | 0 |
> 8 |something |something| 3 | 1 |
>
> I have such table, ho to buid SQL to get such result?
>
> sesion user_id = 1 get 1,2,3,4,6,7
> sesion user_id = 2 get 1,3,4,5,6,7
> sesion user_id = 3 get 1,3,4,6,7,8
>
> Thx all
>
>
>

Re: how to build SQL?

U¿ytkownik "Cowboy (Gregory A. Beamer) - MVP"
napisa³ w wiadomo¶ci
news:965574F3-6489-43D8-A765-08D945D31FA1@microsoft.com...

>
> Does that get it?
>
Yes, thx, it was written a few posts ealier.