universal- and existential quantification in SQL -

-

given relation

x | y ------- | | ii b | ii b | ii

how query for

  1. the set of x there exists y of length 2 (should yield { a, b })
  2. the set of x y have length 2 (should yield { b })

the first 1 easy (note, in examples length_function stands in product-specific length-of-string function in whatever sql database you're using):

 select distinct x relationname length_function(y) = 2;

for second one, there variety of ways approach problem.

 select x relationname group x      having min(length_function(y)) = 2 , max(length_function(y)) = 2

will aggregate x values , filter length of 2 while

 select distinct x relationname length_function(y) = 2     , x not in (select distinct x relationname length_function(y) <> 2)

uses same filter first query, additionally filters out x values exist elsewhere in table non-length-2 y value. finally

 select distinct x relationname rn1 length_function(y) = 2     , not exists (select * relationname x = rn1.x , length_function(y) <> 2)

does same filtering in way may find more expressive (but less performant)


Comments

Post a Comment

Popular posts from this blog

python - Referencing Data From a 2D Histogram -

-
i have following code reads data csv file , creates 2d histogram: import numpy np import pandas pd import matplotlib mpl import matplotlib.pyplot plt #read in csv data filename = 'complete_storms_all_us_only.csv' df = pd.read_csv(filename) min_85 = df.min85 min_37 = df.min37 verification = df.one_min_15 #numbers x = min_85 y = min_37 h = verification #estimate 2d histogram nbins = 33 h, xedges, yedges = np.histogram2d(x,y,bins=nbins) #rotate , flip h h = np.rot90(h) h = np.flipud(h) #mask zeros hmasked = np.ma.masked_where(h==0,h) #calculate averages avgarr = np.zeros((nbins, nbins)) xbins = np.digitize(x, xedges[1:-1]) ybins = np.digitize(y, yedges[1:-1]) xb, yb, v in zip(xbins, ybins, verification): avgarr[yb, xb] += v divisor = h.copy() divisor[divisor==0.0] = np.nan avgarr /= divisor binavg = np.around((avgarr * 100), decimals=1) binper = np.ma.array(binavg, mask=np.isnan(binavg)) #plot 2d histogram using pcolor fig1 = plt.figure() plt.pcolormesh(xedges,yedg...
Read more

c# - Derived UserControl layout resets after build -

-
i have basic "base" usercontrol added properties. when inherit base usercontrol , add buttons in designer seems fine. when build project of buttons disappear , layout restores original base usercontrol. doing wrong here? public partial class baseusercontrol : usercontrol { public baseusercontrol() { initializecomponents(); } public bool canclose { get; set; } } public partial class inheritedusercontrol : baseusercontrol { public inheritedusercontrol() { initializecomponents(); } }
Read more

php - MySQL LIMIT results with INNER JOIN with more than 2 tables -

-
Image
i have database user allowed have more 1 email address. want query database instances of job customer. each job returning twice, 1 each email. here query: select customers.first_name, customers.last_name, emails.email, services.title, services.description, jobs.status, jobs.job_id customers inner join emails on customers.user_id=emails.user_id inner join jobs on customers.user_id=jobs.customer_id inner join services on jobs.service_id=services.service_id; it returning this: i want return results first email only. row should not duplicated each email address user has on file. how can done? you can use group by on sql unique column value follows. select customers.first_name, customers.last_name, emails.email, services.title, services.description, jobs.status customers inner join emails on customers.user_id=emails.user_id inner join jobs on customers.user_id=jobs.customer_id inner join services on jobs.service_id=services.service_id; group jobs.job_i...
Read more